﻿using CsvHelper;
using EfSandbox.Context;
using EfSandbox.Entities.Final.Common;
using EfSandbox.Entities.Final.Eod;
using EfSandbox.Entities.Final.Eod.Raw;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Transactions;

namespace EfSandbox.Migrations {
    public class Initializer : DropCreateDatabaseIfModelChanges<SandboxContext> {

        // Seed from csv: http://www.davepaquette.com/archive/2014/03/18/seeding-entity-framework-database-from-csv.aspx
        
        protected override void Seed(SandboxContext context) {
            Assembly assembly = Assembly.GetExecutingAssembly();

            context.Configuration.AutoDetectChangesEnabled = false;
            /*
            // Time Zone
            string resourceName = "EfSandbox.Migrations.SeedData.TimeZones.csv";
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var data = csvReader.GetRecords<TimeZone>().ToArray();
                    context.TimeZones.AddOrUpdate(data);
                }
            }
            context.SaveChanges();
            
            // Currencies
            resourceName = "EfSandbox.Migrations.SeedData.Currencies.csv";
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var data = csvReader.GetRecords<Currency>().ToArray();
                    context.Currencies.AddOrUpdate(data);
                }
            }
            context.SaveChanges();

            
            // Categories
            resourceName = "EfSandbox.Migrations.SeedData.Frequencies.csv";
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var categories = csvReader.GetRecords<Frequency>().ToArray();
                    context.Categories.AddOrUpdate(categories);
                }
            }
            context.SaveChanges();

            resourceName = "EfSandbox.Migrations.SeedData.OptionTypes.csv";
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    var categories = csvReader.GetRecords<OptionType>().ToArray();
                    context.Categories.AddOrUpdate(categories);
                }
            }
            context.SaveChanges();

            // Countries
            resourceName = "EfSandbox.Migrations.SeedData.Countries.csv";
            using(Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while(csvReader.Read()) {
                        var rec = csvReader.GetRecord<Country>();
                        var code = csvReader.GetField<string>("CurrencyCode");
                        rec.Currency = context.Currencies.Local.Single(c => c.CurrencyCode == code);
                        context.Countries.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();

            // Exchanges
            resourceName = "EfSandbox.Migrations.SeedData.Exchanges.csv";
            using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    CsvReader csvReader = new CsvReader(reader);
                    csvReader.Configuration.WillThrowOnMissingField = false;
                    while (csvReader.Read()) {
                        var rec = csvReader.GetRecord<Exchange>();
                        // Currency Ref
                        var curCode = csvReader.GetField<string>("CurrencyCode");
                        rec.Currency = context.Currencies.Local.SingleOrDefault(c => c.CurrencyCode == curCode);
                        // Country Ref
                        var cntCode = csvReader.GetField<string>("CountryCode");
                        rec.Country = context.Countries.Local.SingleOrDefault(c => c.CountryCode == cntCode);
                        // Timezone Ref
                        var tizCode = csvReader.GetField<string>("TimeZoneCode");
                        rec.TimeZone = context.TimeZones.Local.Single(c => c.TimeZoneCode == tizCode);
                        context.Exchanges.AddOrUpdate(rec);
                    }
                }
            }
            context.SaveChanges();
            */
            // RawSymbols
            using (TransactionScope scope = new TransactionScope() ) {
                
                string resourceName = "EfSandbox.Migrations.SeedData.RawSymbols.csv";
                using (Stream stream = assembly.GetManifestResourceStream(resourceName)) {
                    using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                        CsvReader csvReader = new CsvReader(reader);
                        csvReader.Configuration.WillThrowOnMissingField = false;
                        int counter = 0;
                        while (csvReader.Read()) {
                            ++counter;
                            var rec = csvReader.GetRecord<RawSymbol>();
                            context.RawSymbols.AddOrUpdate(rec);
                            if (counter % 1000 == 0) {
                                context.SaveChanges();
                            }
                        }
                        context.SaveChanges();
                    }
                }
                scope.Complete();
            }

        }

        private void LoadSqlProcedure(SandboxContext context, string ressourceName) {
            Assembly assembly = Assembly.GetExecutingAssembly();
            using (Stream stream = assembly.GetManifestResourceStream(ressourceName)) {
                using(StreamReader reader = new StreamReader(stream, Encoding.UTF8)) {
                    string sql = reader.ReadToEnd();
                    context.Database.ExecuteSqlCommand(sql);
                }
            }
        }
        
        
    }
}
